package com.hxh.util;

import cn.hutool.core.util.StrUtil;
import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlPrimaryKey;
import com.alibaba.druid.sql.dialect.mysql.ast.MySqlUnique;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlTableIndex;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.hxh.config.Constants;
import com.hxh.entity.ProjectDatasource;
import com.hxh.entity.ProjectTablePageConfig;
import com.hxh.entity.po.ClassInfo;
import com.hxh.entity.po.ExplainByResultSet;
import com.hxh.entity.po.FieldName;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

/**
 * @ProjectName: AICode
 * @Package: com.example.util
 * @ClassName: MysqlUtils
 * @Author: HXH
 * @Description:
 * @Date: 2023/12/11 10:02
 */
@Slf4j
public class MysqlUtils {

    public static void executeSql(ProjectDatasource projectDatasource, List<String> sqls) throws Exception {
        ConnetMysql connetMysql = new ConnetMysql();
        Connection connection = connetMysql.getConnection(projectDatasource);
        Statement statement = connection.createStatement();
        for (String s : sqls) {
            try {
                statement.execute(s);
            } catch (Exception e) {
                log.error("sql执行异常", e);
            }
        }
//        6.释放连接
        statement.close();
        connection.close();
    }

    public static void executeOneSql(ProjectDatasource projectDatasource, String sql) throws Exception {
        ConnetMysql connetMysql = new ConnetMysql();
        Connection connection = connetMysql.getConnection(projectDatasource);
        Statement statement = connection.createStatement();
        try {
            statement.execute(sql);
        } catch (Exception e) {
            log.error("sql执行异常", e);
        }
//        6.释放连接
        statement.close();
        connection.close();
    }

    public static void executeOneSqlWithException(ProjectDatasource projectDatasource, String sql) throws Exception {
        ConnetMysql connetMysql = new ConnetMysql();
        Connection connection = connetMysql.getConnection(projectDatasource);
        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
        connection.close();
    }

    public static Integer executeInsertSqlWithId(ProjectDatasource projectDatasource, String sql) throws Exception {
        ConnetMysql connetMysql = new ConnetMysql();
        Connection connection = connetMysql.getConnection(projectDatasource);
        Statement statement = connection.createStatement();
        try {
            statement.execute(sql);
            ResultSet resultSet = statement.executeQuery("SELECT LAST_INSERT_ID();");
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
            return null;
        } catch (Exception e) {
            log.error("sql执行异常", e);
            return null;
        } finally {
            //        6.释放连接
            statement.close();
            connection.close();
        }
    }

    public static void executeCreateDatabaseSql(ProjectDatasource projectDatasource, String sql) throws Exception {
        ConnetMysql connetMysql = new ConnetMysql();
        String connectUrl = projectDatasource.getMysqlConnectUrl();
        String oriConnectUrl = connectUrl;
        String databaseName = projectDatasource.getMysqlDatabaseName();
        connectUrl = connectUrl.replace("/" + databaseName, "");
        projectDatasource.setMysqlConnectUrl(connectUrl);
        Connection connection = connetMysql.getConnection(projectDatasource);
        Statement statement = connection.createStatement();
        try {
            statement.execute(sql);
        } catch (Exception e) {
            log.error("sql执行异常", e);
        } finally {
            projectDatasource.setMysqlConnectUrl(oriConnectUrl);
        }
//        6.释放连接
        statement.close();
        connection.close();
    }

    public static <T extends ExplainByResultSet<T>> List<T> executeOneQuery(ProjectDatasource projectDatasource, String sql, Class<T> clazz) throws Exception {
        ConnetMysql connetMysql = new ConnetMysql();
        Connection connection = connetMysql.getConnection(projectDatasource);
        List<T> list = executeOneQuery(connection, sql, clazz);
        connection.close();
        return list;
    }

    public static <T extends ExplainByResultSet<T>> List<T> executeOneQuery(Connection connection, String sql, Class<T> clazz) throws Exception {
        Statement statement = connection.createStatement();
//        5.执行SQL的对象去执行SQL，返回结果集
        ResultSet resultSet = statement.executeQuery(sql);

        List<T> list = new ArrayList<>();
        while (resultSet.next()) {
            T t = clazz.newInstance();
            t = t.getOneByRs(resultSet);
            list.add(t);
        }
//        6.释放连接
        resultSet.close();
        statement.close();
        return list;
    }

    public static Object queryOneAndReturnFirstResult(ProjectDatasource projectDatasource, String sql) throws Exception {
        ConnetMysql connetMysql = new ConnetMysql();
        Connection connection = connetMysql.getConnection(projectDatasource);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        Object res = null;
        while (resultSet.next()) {
            res = resultSet.getObject(1);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return res;
    }


    /**
     * @param path: 文件路径
     * @return java.util.List<java.lang.String> sql集合
     * @author HXH
     * @description: 读取文件中的sql，转成sql语句集合，忽略#开头的行
     * @date 2024/5/21
     **/
    public static List<String> splitSql(String path) throws Exception {
        File sqlFile = new File(path);
        if (!sqlFile.exists()) {
            return null;
//            throw new RuntimeException("sql文件不存在：" + path);
        }
        BufferedReader br = new BufferedReader(new FileReader(sqlFile));

        List<String> sqls = new ArrayList<>();

        StringBuilder stringBuilder = new StringBuilder();
        String line;
        while ((line = br.readLine()) != null) {
            if (StringUtils.isNotBlank(line)) {
                if (line.startsWith("#")) {
                    continue;
                }
                stringBuilder.append(line).append(" ");
            }
            if (line.contains(";")) {
                sqls.add(stringBuilder.toString());
                stringBuilder.setLength(0);
            }
        }
        return sqls;
    }

    /**
     * @param sql: 建表sql
     * @return com.example.entity.generation.po.ClassInfo
     * @author HXH
     * @description: 通过mysql的建表语句生成ClassInfo对象
     * @date 2024/3/21
     **/
    public static ClassInfo generationClassInfoByCreateTableSql(String sql) {
        SQLStatementParser parser = new MySqlStatementParser(sql);
        SQLCreateTableStatement sqlCreateTableStatement = parser.parseCreateTable();
        List<SQLObject> sqlObjects = sqlCreateTableStatement.getChildren();

        ClassInfo classInfo = new ClassInfo();

        List<FieldName> fieldNameList = new ArrayList<>();
        List<FieldName> idNameList = new ArrayList<>();
        List<String> idList = new ArrayList<>();
        Map<String, String> uniIndexMap = new HashMap<>();
        Map<String, String> mulIndexMap = new HashMap<>();

        String tableName = sqlCreateTableStatement.getTableSource().toString().replaceAll("`", "");

        String tableDesc = "";
        if (sqlCreateTableStatement.getComment() != null) {
            tableDesc = processComment(sqlCreateTableStatement.getComment().toString());
        }
        classInfo.setClassName(StringUtils.capitalize(StrUtil.toCamelCase(tableName)));
        classInfo.setTableName(tableName);
        classInfo.setDesc(tableDesc);

        for (SQLObject sqlObject : sqlObjects) {
            FieldName fieldName = new FieldName();
            if (sqlObject instanceof SQLColumnDefinition) {
                SQLColumnDefinition columnDefinition = ((SQLColumnDefinition) sqlObject);
                String fn = columnDefinition.getNameAsString();
                fn = processComment(fn);
                fieldName.setName(fn);
                fieldName.setJavaName(StrUtil.toCamelCase(fn));

                String ftype = columnDefinition.getDataType().toString();
                fieldName.setType(ftype);
                fieldName.setJavaType(fieldName.jdbcTypeToJavaType(ftype));

                String fdesc = columnDefinition.getComment() == null ? null : columnDefinition.getComment().toString();
                fieldName.setDesc(processComment(fdesc));

                String fdefaultValue = columnDefinition.getDefaultExpr() == null ? null : columnDefinition.getDefaultExpr().toString();
                fdefaultValue = processComment(fdefaultValue);
                if ("null".equalsIgnoreCase(fdefaultValue)) {
                    fdefaultValue = null;
                }
                fieldName.setDefaultValue(fdefaultValue);
                fieldName.setAutIncrement(columnDefinition.isAutoIncrement());

                fieldName.setNotNull(false);
                List<SQLColumnConstraint> list = ((SQLColumnDefinition) sqlObject).getConstraints();
                if (!CollectionUtils.isEmpty(list)) {
                    for (SQLColumnConstraint sqlColumnConstraint : list) {
                        if (sqlColumnConstraint.getClass().getName().toLowerCase().contains("notnull")) {
                            fieldName.setNotNull(true);
                        }
                    }
                }
                fieldNameList.add(fieldName);
            } else if (sqlObject instanceof SQLPrimaryKey) {
                List<SQLSelectOrderByItem> list = ((MySqlPrimaryKey) sqlObject).getColumns();
                if (!CollectionUtils.isEmpty(list)) {
                    for (SQLSelectOrderByItem sqlSelectOrderByItem : list) {
                        String primaryKeyName = sqlSelectOrderByItem.getExpr().toString();
                        primaryKeyName = processComment(primaryKeyName);
                        idList.add(primaryKeyName);
                    }
                }
            } else if (sqlObject instanceof SQLUnique) {
                List<SQLSelectOrderByItem> list = ((MySqlUnique) sqlObject).getColumns();
                if (!CollectionUtils.isEmpty(list)) {
                    String[] keys = new String[list.size()];
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < list.size(); i++) {
                        SQLSelectOrderByItem sqlSelectOrderByItem = list.get(i);
                        String indexKeyName = sqlSelectOrderByItem.getExpr().toString();
                        indexKeyName = processComment(indexKeyName);
                        keys[i] = indexKeyName;
                        sb.append(indexKeyName).append(",");
                    }
                    if (sb.length() > 0) {
                        sb.deleteCharAt(sb.length() - 1);
                    }
                    for (String key : keys) {
                        uniIndexMap.put(key, sb.toString());
                    }
                }
            } else if (sqlObject instanceof MySqlTableIndex) {
                List<SQLSelectOrderByItem> list = ((MySqlTableIndex) sqlObject).getColumns();
                if (!CollectionUtils.isEmpty(list)) {
                    String[] keys = new String[list.size()];
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < list.size(); i++) {
                        SQLSelectOrderByItem sqlSelectOrderByItem = list.get(i);
                        String indexKeyName = sqlSelectOrderByItem.getExpr().toString();
                        indexKeyName = processComment(indexKeyName);
                        keys[i] = indexKeyName;
                        sb.append(indexKeyName).append(",");
                    }
                    if (sb.length() > 0) {
                        sb.deleteCharAt(sb.length() - 1);
                    }
                    for (String key : keys) {
                        mulIndexMap.put(key, sb.toString());
                    }
                }
            }
        }

        for (Map.Entry<String, String> stringStringEntry : uniIndexMap.entrySet()) {
            for (FieldName fieldName : fieldNameList) {
                if (fieldName.getName().equals(stringStringEntry.getKey())) {
                    fieldName.setKey("UNI[" + stringStringEntry.getValue() + "]");
                    break;
                }
            }
        }

        for (Map.Entry<String, String> stringStringEntry : mulIndexMap.entrySet()) {
            for (FieldName fieldName : fieldNameList) {
                if (fieldName.getName().equals(stringStringEntry.getKey())) {
                    fieldName.setKey("MUL[" + stringStringEntry.getValue() + "]");
                    break;
                }
            }
        }

        for (String id : idList) {
            for (FieldName fieldName : fieldNameList) {
                if (fieldName.getName().equals(id)) {
                    fieldName.setKey(Constants.MYSQL_PRIMARY_KEY_NAME);
                    idNameList.add(fieldName);
                    break;
                }
            }
        }
        classInfo.setFieldNameList(fieldNameList);
        classInfo.setIdNameList(idNameList);
        classInfo.setProjectTablePageConfig(ProjectTablePageConfig.generationDefaultByClassInfo(classInfo));
        return classInfo;
    }

    /**
     * @param comment:
     * @return java.lang.String
     * @author HXH
     * @description: 处理generationClassInfoByCreateTableSql方法的注释
     * @date 2024/3/21
     **/
    private static String processComment(String comment) {
        if (StrUtil.isBlank(comment)) {
            return comment;
        }
        if (comment.toLowerCase().contains("collate")) {
            int index = comment.toLowerCase().indexOf("collate");
            comment = comment.substring(0, index);
        }
        comment = comment.trim();
        if (comment.startsWith("`")) {
            comment = comment.substring(1);
        }
        if (comment.startsWith("'")) {
            comment = comment.substring(1);
        }
        if (comment.endsWith("'")) {
            comment = comment.substring(0, comment.length() - 1);
        }
        if (comment.endsWith("`")) {
            comment = comment.substring(0, comment.length() - 1);
        }
        comment = comment.trim();
        return comment;
    }


    /**
     * 通过对象，生成对象的更新语句
     *
     * @param object
     * @param tableName   表名 如果为空，则取对象的类的名字，并且驼峰转下划线
     * @param primaryKeys 主键id,用逗号连接，不能为空
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> String genUpdateSql(T object, String tableName, String primaryKeys) throws Exception {
        if (StrUtil.isBlank(primaryKeys)) {
            throw new RuntimeException("自动生成更新语句中，主键字段不能为空！");
        }
        List<String> primaryKeyList = Arrays.asList(primaryKeys.split(","));
        Class<? extends Object> tClass = object.getClass();
        if (StringUtils.isBlank(tableName)) {
            tableName = tClass.getName();
            tableName = StrUtil.toSymbolCase(tableName, '_');
        }
        String sql = "update `" + tableName + "` set ";
        StringBuilder column = new StringBuilder();
        StringBuilder whereColumn = new StringBuilder();
        Field[] fields = tClass.getDeclaredFields();
        for (Field field : fields) {
            String fieldName = field.getName();
            Method method = tClass.getDeclaredMethod("get" + StrUtil.upperFirst(fieldName));
            method.setAccessible(true);
            Object value = method.invoke(object);
            if (value != null) {
                String tableFieldName = StrUtil.toSymbolCase(fieldName, '_');
                if (primaryKeyList.contains(fieldName)) {
                    whereColumn.append(" `").append(tableFieldName).append("`=");
                    whereColumn.append("'").append(value).append("' and");
                } else {
                    column.append("`").append(tableFieldName).append("` = ");
                    if (value instanceof Date) {
                        String dateStr = DateUtils.getDateStrYMDHMS((Date) value);
                        column.append("'").append(dateStr).append("',");
                    } else {
                        column.append("'").append(value).append("',");
                    }
                }
            }
        }
        if (column.length() <= 0) {
            throw new RuntimeException("对象为空！解析失败！");
        }
        column.deleteCharAt(column.length() - 1);
        String whereColumnStr = whereColumn.substring(0, whereColumn.length() - 3);
        sql = sql + column + " WHERE " + whereColumnStr + " ;";
        return sql;
    }

    /**
     * 通过对象，生成对象的插入语句
     *
     * @param object
     * @param tableName 表名 如果为空，则取对象的类的名字，并且驼峰转下划线
     * @param <T>
     * @return sql 语句
     * @throws Exception
     */
    public static <T> String genInsertSql(T object, String tableName) throws Exception {
        Class<? extends Object> tClass = object.getClass();
        if (StringUtils.isBlank(tableName)) {
            tableName = tClass.getName();
            tableName = StrUtil.toSymbolCase(tableName, '_');
        }
        String sql = "insert into `" + tableName + "` ";
        StringBuilder column = new StringBuilder();
        StringBuilder values = new StringBuilder();
        Field[] fields = tClass.getDeclaredFields();
        for (Field field : fields) {
            String fieldName = field.getName();
            Method method = tClass.getDeclaredMethod("get" + StrUtil.upperFirst(fieldName));
            method.setAccessible(true);
            Object value = method.invoke(object);
            if (value != null) {
                column.append("`").append(StrUtil.toSymbolCase(fieldName, '_')).append("`,");
                if (value instanceof Date) {
                    String dateStr = DateUtils.getDateStrYMDHMS((Date) value);
                    values.append("'").append(dateStr).append("',");
                } else {
                    values.append("'").append(value).append("',");
                }
            }
        }
        if (column.length() <= 0) {
            throw new RuntimeException("对象为空！解析失败！");
        }
        column.deleteCharAt(column.length() - 1);
        values.deleteCharAt(values.length() - 1);
        sql = sql + "(" + column + ")" + " values(" + values + ");";
        return sql;
    }
}
